package net.suncaper.mvc.mapper.impl;

import net.suncaper.mvc.mapper.UserMapper;
import net.suncaper.mvc.model.domain.User;
import net.suncaper.mvc.utils.JDBCUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import java.sql.Connection;
import java.sql.SQLException;
import java.util.List;

/**
 * User表数据库访问接口实现类
 */
public class UserMapperImpl implements UserMapper {
    private static final QueryRunner RUNNER = new QueryRunner();

    /**
     * 新增用户信息
     *
     * @param user 用户信息
     * @return 新增结果
     */
    @Override
    public boolean insertUser(User user) {
        // 插入用户表
        String sql = "INSERT INTO user(user_id, email, password, avatar, status, gender, age, create_time) VALUES (?, ?, ?, ?, ?, ?, ?, ?)";
        Connection connection = JDBCUtils.getConnection();
        int rows = 0;
        try {
            rows = RUNNER.update(
                    connection,
                    sql,
                    user.getUserId(), user.getEmail(), user.getPassword(), 
                    user.getAvatar(), user.getStatus(), user.getGender(),
                    user.getAge(), user.getCreateTime()
            );
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            JDBCUtils.close();
        }
        return rows > 0;
    }

    /**
     * 新增用户角色关联
     *
     * @param userId 用户ID
     * @param roleId 角色ID
     * @param hotelName 酒店名称（可为空）
     * @return 新增结果
     */
    @Override
    public boolean insertUserRole(String userId, Integer roleId, String hotelName) {
        String sql = "INSERT INTO user_role(user_id, role_id, hotel_name) VALUES (?, ?, ?)";
        Connection connection = JDBCUtils.getConnection();
        int rows = 0;
        try {
            rows = RUNNER.update(
                    connection,
                    sql,
                    userId, roleId, hotelName
            );
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            JDBCUtils.close();
        }
        return rows > 0;
    }

    /**
     * 根据用户ID查询用户信息
     *
     * @param userId 用户ID
     * @return 用户信息
     */
    @Override
    public User selectUserById(String userId) {
        String sql = "SELECT * FROM user WHERE user_id = ?";
        Connection connection = JDBCUtils.getConnection();
        User user = null;
        java.sql.PreparedStatement ps = null;
        java.sql.ResultSet rs = null;
        try {
            ps = connection.prepareStatement(sql);
            ps.setString(1, userId);
            rs = ps.executeQuery();
            if (rs.next()) {
                user = new User();
                user.setUserId(rs.getString("user_id"));
                user.setEmail(rs.getString("email"));
                user.setPassword(rs.getString("password"));
                user.setAvatar(rs.getString("avatar"));
                user.setStatus(rs.getInt("status"));
                user.setGender(rs.getInt("gender"));
                user.setAge(rs.getInt("age"));
                user.setCreateTime(rs.getTimestamp("create_time"));
                
                System.out.println("[selectUserById] user_id=" + rs.getString("user_id") 
                    + ", email=" + rs.getString("email"));
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            try { if (rs != null) rs.close(); } catch (Exception e) {}
            try { if (ps != null) ps.close(); } catch (Exception e) {}
            JDBCUtils.close();
        }
        return user;
    }

    /**
     * 根据邮箱查询用户信息
     *
     * @param email 邮箱
     * @return 用户信息
     */
    @Override
    public User selectUserByEmail(String email) {
        String sql = "SELECT * FROM user WHERE email = ?";
        Connection connection = JDBCUtils.getConnection();
        User user = null;
        java.sql.PreparedStatement ps = null;
        java.sql.ResultSet rs = null;
        try {
            ps = connection.prepareStatement(sql);
            ps.setString(1, email);
            rs = ps.executeQuery();
            if (rs.next()) {
                user = new User();
                user.setUserId(rs.getString("user_id"));
                user.setEmail(rs.getString("email"));
                user.setPassword(rs.getString("password"));
                user.setAvatar(rs.getString("avatar"));
                user.setStatus(rs.getInt("status"));
                user.setGender(rs.getInt("gender"));
                user.setAge(rs.getInt("age"));
                user.setCreateTime(rs.getTimestamp("create_time"));
                
                System.out.println("[selectUserByEmail] user_id=" + rs.getString("user_id") 
                    + ", email=" + rs.getString("email"));
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            try { if (rs != null) rs.close(); } catch (Exception e) {}
            try { if (ps != null) ps.close(); } catch (Exception e) {}
            JDBCUtils.close();
        }
        return user;
    }

    /**
     * 查询所有用户信息
     *
     * @return 用户列表
     */
    @Override
    public java.util.List<User> selectAllUsers() {
        String sql = "SELECT * FROM user";
        Connection connection = JDBCUtils.getConnection();
        java.util.List<User> users = new java.util.ArrayList<>();
        java.sql.PreparedStatement ps = null;
        java.sql.ResultSet rs = null;
        try {
            ps = connection.prepareStatement(sql);
            rs = ps.executeQuery();
            while (rs.next()) {
                User user = new User();
                user.setUserId(rs.getString("user_id"));
                user.setEmail(rs.getString("email"));
                user.setPassword(rs.getString("password"));
                user.setAvatar(rs.getString("avatar"));
                user.setStatus(rs.getInt("status"));
                user.setGender(rs.getInt("gender"));
                user.setAge(rs.getInt("age"));
                user.setCreateTime(rs.getTimestamp("create_time"));
                
                System.out.println("[selectAllUsers] user_id=" + rs.getString("user_id") 
                    + ", email=" + rs.getString("email"));
                users.add(user);
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            try { if (rs != null) rs.close(); } catch (Exception e) {}
            try { if (ps != null) ps.close(); } catch (Exception e) {}
            JDBCUtils.close();
        }
        return users;
    }

    /**
     * 更新用户信息
     *
     * @param user 用户信息
     * @return 更新结果
     */
    @Override
    public boolean updateUser(User user) {
        String sql = "UPDATE user SET email = ?, password = ?, avatar = ?, status = ?, gender = ?, age = ? WHERE user_id = ?";
        Connection connection = JDBCUtils.getConnection();
        int rows = 0;
        try {
            rows = RUNNER.update(
                    connection,
                    sql,
                    user.getEmail(), user.getPassword(), user.getAvatar(),
                    user.getStatus(), user.getGender(), user.getAge(),
                    user.getUserId()
            );
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            JDBCUtils.close();
        }
        return rows > 0;
    }

    /**
     * 删除用户信息
     *
     * @param userId 用户ID
     * @return 删除结果
     */
    @Override
    public boolean deleteUser(String userId) {
        // 修正字段名：UserId→user_id；表名改为user
        String sql = "DELETE FROM user WHERE user_id = ?";
        Connection connection = JDBCUtils.getConnection();
        int rows = 0;
        try {
            rows = RUNNER.update(
                    connection,
                    sql,
                    userId
            );
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            JDBCUtils.close();
        }
        return rows > 0;
    }

    /**
     * 判断邮箱是否已经存在
     *
     * @param email 邮箱
     * @return 邮箱是否存在
     */
    @Override
    public boolean isEmailExists(String email) {
        // 修正字段名：Email→email；表名改为user
        String sql = "SELECT COUNT(*) FROM user WHERE email = ?";
        Connection connection = JDBCUtils.getConnection();
        Long count = 0L;
        try {
            count = RUNNER.query(
                    connection,
                    sql,
                    new ScalarHandler<>(),
                    email
            );
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            JDBCUtils.close();
        }
        return count > 0;
    }

    /**
     * 根据用户ID删除用户角色关联
     *
     * @param userId 用户ID
     * @return 删除结果
     */
    @Override
    public boolean deleteUserRoleByUserId(String userId) {
        String sql = "DELETE FROM user_role WHERE user_id = ?";
        Connection connection = JDBCUtils.getConnection();
        int rows = 0;
        try {
            rows = RUNNER.update(
                    connection,
                    sql,
                    userId
            );
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            JDBCUtils.close();
        }
        return rows > 0;
    }

    /**
     * 查询用户是否已有角色关联
     *
     * @param userId 用户ID
     * @return 是否存在关联
     */
    @Override
    public boolean isUserRoleExists(String userId) {
        String sql = "SELECT COUNT(*) FROM user_role WHERE user_id = ?";
        Connection connection = JDBCUtils.getConnection();
        Long count = 0L;
        try {
            count = RUNNER.query(
                    connection,
                    sql,
                    new org.apache.commons.dbutils.handlers.ScalarHandler<>(),
                    userId
            );
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            JDBCUtils.close();
        }
        return count > 0;
    }

    /**
     * 更新用户角色关联的酒店名称
     *
     * @param userId 用户ID
     * @param hotelName 酒店名称
     * @return 更新结果
     */
    @Override
    public boolean updateHotelNameForUser(String userId, String hotelName) {
        String sql = "UPDATE user_role SET hotel_name = ? WHERE user_id = ?";
        Connection connection = JDBCUtils.getConnection();
        int rows = 0;
        try {
            rows = RUNNER.update(
                    connection,
                    sql,
                    hotelName, userId
            );
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            JDBCUtils.close();
        }
        return rows > 0;
    }

    /**
     * 根据用户ID查询用户角色信息
     *
     * @param userId 用户ID
     * @return 角色ID
     */
    @Override
    public Integer selectRoleIdByUserId(String userId) {
        String sql = "SELECT role_id FROM user_role WHERE user_id = ?";
        Connection connection = JDBCUtils.getConnection();
        Integer roleId = null;
        try {
            // 获取结果为Long类型，转换为Integer
            Long longValue = RUNNER.query(
                    connection,
                    sql,
                    new ScalarHandler<Long>(),
                    userId
            );
            
            // 如果查询结果不为空，转换为Integer
            if (longValue != null) {
                roleId = longValue.intValue();
            }
        } catch (SQLException e) {
            // 如果没有找到记录，ScalarHandler会抛出异常，这里需要捕获
            System.out.println("[selectRoleIdByUserId] 没有找到用户角色信息: userId=" + userId);
        } finally {
            JDBCUtils.close();
        }
        return roleId;
    }

    /**
     * 根据用户ID查询用户关联的酒店名称
     *
     * @param userId 用户ID
     * @return 酒店名称
     */
    @Override
    public String selectHotelNameByUserId(String userId) {
        String sql = "SELECT hotel_name FROM user_role WHERE user_id = ?";
        Connection connection = JDBCUtils.getConnection();
        String hotelName = null;
        try {
            hotelName = RUNNER.query(
                    connection,
                    sql,
                    new ScalarHandler<String>(),
                    userId
            );
        } catch (SQLException e) {
            // 如果没有找到记录，ScalarHandler会抛出异常，这里需要捕获
            System.out.println("[selectHotelNameByUserId] 没有找到用户关联酒店: userId=" + userId);
        } finally {
            JDBCUtils.close();
        }
        return hotelName;
    }
}